package com.base.utils;
 
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * 描述：Excel写操作帮助类
 * @author Administrator
 *
 */
public class ExcelUtil {
    private static final Logger log=Logger.getLogger(ExcelUtil.class);
    /**
     * 功能：将HSSFWorkbook写入Excel文件
     * @param   wb      HSSFWorkbook
     * @param   absPath 写入文件的相对路径
     * @param   wbName  文件名
     */
    public static void writeWorkbook(HSSFWorkbook wb,String fileName){
        FileOutputStream fos=null;
        try {
            fos=new FileOutputStream(fileName);
            wb.write(fos);
        } catch (FileNotFoundException e) {
            log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
        } catch (IOException e) {
            log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
        } finally{
            try {
                if(fos!=null){
                    fos.close();
                }
            } catch (IOException e) {
                log.error(new StringBuffer("[").append(e.getMessage()).append("]").append(e.getCause()));
            }
        }
    }
    /**
     * 功能：创建HSSFSheet工作簿
     * @param   wb  HSSFWorkbook
     * @param   sheetName   String
     * @return  HSSFSheet
     */
    public static HSSFSheet createSheet(HSSFWorkbook wb,String sheetName){
        HSSFSheet sheet=wb.createSheet(sheetName);
        sheet.setDefaultColumnWidth(12);
        sheet.setGridsPrinted(false);
        sheet.setDisplayGridlines(false);
        return sheet;
    }
    /**
     * 功能：创建HSSFRow
     * @param   sheet   HSSFSheet
     * @param   rowNum  int
     * @param   height  int
     * @return  HSSFRow
     */
    public static HSSFRow createRow(HSSFSheet sheet,int rowNum,int height){
        HSSFRow row=sheet.createRow(rowNum);
        row.setHeight((short)height);
        return row;
    }
    /**
     * 功能：创建CellStyle样式
     * @param   wb              HSSFWorkbook    
     * @param   backgroundColor 背景色 
     * @param   foregroundColor 前置色
     * @param   font            字体
     * @return  CellStyle
     */
    public static HSSFCellStyle createCellStyle(HSSFWorkbook wb,short backgroundColor,
    		short foregroundColor,short halign,Font font){
    	HSSFCellStyle cs=wb.createCellStyle();
        cs.setAlignment(halign);
        cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cs.setFillBackgroundColor(backgroundColor);
        cs.setFillForegroundColor(foregroundColor);
        cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cs.setFont(font);
        return cs;
    }
    /**
     * 功能：创建带边框的CellStyle样式
     * @param   wb              HSSFWorkbook    
     * @param   backgroundColor 背景色 
     * @param   foregroundColor 前置色
     * @param   font            字体
     * @return  CellStyle
     */
    public static HSSFCellStyle createBorderCellStyle(HSSFWorkbook wb,short backgroundColor,
    		short foregroundColor,short halign, Font font){
    	HSSFCellStyle cs=wb.createCellStyle();
//        cs.setFillBackgroundColor(backgroundColor);
        cs.setFillForegroundColor(foregroundColor);
        cs.setFont(font);
        cs.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cs.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cs.setAlignment(halign);
        cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//        cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cs.setWrapText(true);
        return cs;
    }
    /**
     * 功能：创建CELL
     * @param   row     HSSFRow 
     * @param   cellNum int
     * @param   style   HSSFStyle
     * @return  HSSFCell
     */
    public static HSSFCell createCell(HSSFRow row,int cellNum,CellStyle style){
        HSSFCell cell=row.createCell(cellNum);
        cell.setCellStyle(style);
        return cell;
    }
    /**
     * 功能：合并单元格
     * @param   sheet       HSSFSheet
     * @param   firstRow    int
     * @param   lastRow     int
     * @param   firstColumn int
     * @param   lastColumn  int
     * @return  int         合并区域号码
     */
    public static int mergeCell(HSSFSheet sheet,int firstRow,int lastRow,int firstColumn,int lastColumn){
        return sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstColumn,lastColumn));    
    }
    /**
     * 功能：创建字体
     * @param   wb          HSSFWorkbook    
     * @param   boldweight  short
     * @param   color       short
     * @return  Font    
     */
    public static HSSFFont createFont(HSSFWorkbook wb,short boldweight,short color,short size,String fontName){
    	HSSFFont font=wb.createFont();
        font.setBoldweight(boldweight);
        font.setColor(color);
        font.setFontHeight(size);
        font.setFontName(fontName);
        return font;
    }
    /**
     * 设置合并单元格的边框样式
     * @param   sheet   HSSFSheet   
     * @param   ca      CellRangAddress
     * @param   style   CellStyle
     */
    public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress ca,CellStyle style) {  
        for (int i = ca.getFirstRow(); i <= ca.getLastRow(); i++) {  
            HSSFRow row = HSSFCellUtil.getRow(i, sheet);  
            for (int j = ca.getFirstColumn(); j <= ca.getLastColumn(); j++) {  
                HSSFCell cell = HSSFCellUtil.getCell(row, j);  
                cell.setCellStyle(style);  
            }  
        }  
    }
    /*************读取Excel*********************/
    
    /**
     * 获取合并单元格的值 
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet ,int row , int column){    
        int sheetMergeCount = sheet.getNumMergedRegions();    
        for(int i = 0 ; i < sheetMergeCount ; i++){    
            CellRangeAddress ca = sheet.getMergedRegion(i);    
            int firstColumn = ca.getFirstColumn();    
            int lastColumn = ca.getLastColumn();    
            int firstRow = ca.getFirstRow();    
            int lastRow = ca.getLastRow();    
            if(row >= firstRow && row <= lastRow){    
                if(column >= firstColumn && column <= lastColumn){    
                    Row fRow = sheet.getRow(firstRow);    
                    Cell fCell = fRow.getCell(firstColumn);    
                    return getCellValue(fCell) ;    
                }    
            }    
        }    
            
        return null ;    
    }    
    
    /**  
    * 获取一个单元格的值 
    * @param sheet   
    * @param row 行下标  
    * @param column 列下标  
    * @return  
    */ 
    public static String getSingleCellValue(Sheet sheet, int row, int column) {
		return getCellValue(sheet.getRow(row).getCell(column));
	}
    
    /**   
    * 获取单元格的值   
    * @param cell   
    * @return   
    */    
    public static String getCellValue(Cell cell){    
        if(cell == null) return "";    
        if(cell.getCellType() == Cell.CELL_TYPE_STRING){    
            return cell.getStringCellValue();    
        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){    
            return String.valueOf(cell.getBooleanCellValue());    
        }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){    
            return cell.getCellFormula() ;    
        }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
        	if(cell.getCellStyle().getDataFormat() == 14){
        		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
                double value = cell.getNumericCellValue();  
                Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);  
                return sdf.format(date);  
        	}else{
        		return String.valueOf((int)cell.getNumericCellValue());
        	}
        }   
        return "";    
    }
    
    /**
     * 区别double和int类型值
     * @param cellValue
     * @param type
     * @return
     */
    public static String formatByType(String cellValue,int type){
    	if(type==1){
    		double value = Double.parseDouble(cellValue);  
    		DecimalFormat format = new DecimalFormat(); 
    		format.applyPattern("#"); 
    		return format.format(value);
    	}else{
    		return cellValue;
    	}
    }
    
    /**
	 * 格式化读取excel单元格数据
	 */
	public static String getFormatString(Cell cell) {
		if (cell == null)
			return "";

		if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
			Integer i = (int) cell.getNumericCellValue();
			return i.toString();
		} else {
			return cell.getRichStringCellValue().getString().trim()
					.replaceAll(" ", "");
		}
	}
}